<?php

// CONNECTION FUNCTIONS -------------------------------------------------------------------------------------------------------------------

$s = "localhost";
$u = "root";
$p = "";
$d = "gradegriddatabase";

// FUNCTION 1: function for connecting to database on a server ----------------------------------------------------------------------------



	function connect($server,$username,$password,$database)
	{
		global $s, $u, $p, $d;
		$connect = mysql_connect($s,$u,$p) or die("Server connection failed.");
		mysql_select_db($d) or die("Database connection failed.");
	}



// CREATE FUNCTIONS -----------------------------------------------------------------------------------------------------------------------



// FUNCTION C1: function for creating an account-----------------------------------------------------------------------------------------



	function createaccount($type,$email,$username,$password)
	{
		$createaccount_query = "";
		$createaccount_query_[0] = "INSERT INTO account(type,email,username,password) ";
		$createaccount_query_[1] = "VALUES('".$type."','".$email."','".$username."','".$password."')";
		$createaccount_query = $createaccount_query_[0].$createaccount_query_[1];

		$read = mysql_query("SELECT count(username) as value  FROM account WHERE username='$username' OR email = '$email'") 
			or die(mysql_error());

		while($row = mysql_fetch_assoc($read))
		{
			if (($row['value']==0)or($row['value']=='0'))
			{$insert = mysql_query($createaccount_query) or die(mysql_error());}
			else
			{echo "Username or email already exists.";}
		}
			
		
	}



// FUNCTION C2: function for creating a school -----------------------------------------------------------------------------------------------



	function createschool($account_id,$school_name)
	{
		$createschool_query = "";
		$createschool_query_[0] = "INSERT INTO school(account_id,school_name) ";
		$createschool_query_[1] = "VALUES('".$account_id."','". $school_name."')";
		$createschool_query = $createschool_query_[0].$createschool_query_[1];
		$insert = mysql_query($createschool_query) or die (mysql_error());
	}



// FUNTION C3: function for creating a course -----------------------------------------------------------------------------------------------



	function createcourse($school_id,$account_id,$course_name, $schemeid)
	{
		$createcourse_query_[0] = "INSERT INTO course(school_id,account_id,course_name, schemeid) ";
		$createcourse_query_[1] = "VALUES('".$school_id."','".$account_id."','".$course_name."', '" . $schemeid . "')";
		$createcourse_query = $createcourse_query_[0].$createcourse_query_[1];
		$insert = mysql_query($createcourse_query) or die(mysql_error());
	}



// FUNCTION C4: function for creating a scheme ----------------------------------------------------------------------------------------



	function createscheme($scheme_name,$account_id)
	{
		$createscheme_query_[0] = "INSERT INTO scheme(scheme_name,account_id) ";
		$createscheme_query_[1] = "VALUES('".$scheme_name."','".$account_id."')";
		$createscheme_query = $createscheme_query_[0].$createscheme_query_[1];
		$insert = mysql_query($createscheme_query) or die(mysql_error());	
	}



// FUNCTION C5: function for creating a block ----------------------------------------------------------------------------------------------



	function createblock($scheme_id,$lower_bound,$upper_bound,$mark,$lower_condition,$upper_condition)
	{
		$createblock_query_[0] = "INSERT INTO block(scheme_id,lower_bound,upper_bound,mark,lower_condition,upper_condition) ";
		$createblock_query_[1] = "VALUES ('".$scheme_id."','".$lower_bound."','".$upper_bound."','".$mark."','";
		$createblock_query_[2] = $lower_condition."','".$upper_condition."')";
		$createblock_query = $createblock_query_[0].$createblock_query_[1].$createblock_query_[2];
		$insert = mysql_query($createblock_query) or die(mysql_error());
	}



// FUNCTION C6: function for creating a template ------------------------------------------------------------------------------------------


	
	function createtemplate($template_name,$account_id)
	{
		$createtemplate_query = "";
		$createtemplate_query_[0] = "INSERT INTO template(template_name,account_id) VALUES('";
		$createtemplate_query_[1] = $template_name."','".$account_id."')";
		$createtemplate_query = $createtemplate_query_[0].$createtemplate_query_[1];
		$insert = mysql_query($createtemplate_query) or die(mysql_error());
	}



// FUNCTION C7: function for creating a major requirement ---------------------------------------------------------------------------------



	function createmajor($template_id,$name,$percentage)
	{
		$createmajor_query = "";
		$createmajor_query_[0] = "INSERT INTO major(template_id,name,percentage) VALUES('";
		$createmajor_query_[1] = $template_id."','".$name."','".$percentage."')";
		$createmajor_query = $createmajor_query_[0].$createmajor_query_[1];
		$insert = mysql_query($createmajor_query) or die(mysql_error());
	}



// FUNCTION C8: function for creating a minor requirement ---------------------------------------------------------------------------------



	function createminor($major_id,$name,$maxrawscore)
	{
		$createminor_query = "";
		$createminor_query_[0] = "INSERT INTO minor(major_id,name,maxrawscore) VALUES('";
		$createminor_query_[1] = $major_id."','".$name."','".$maxrawscore."')";
		$createminor_query = $createminor_query_[0].$createminor_query_[1];
		$insert = mysql_query($createminor_query) or die(mysql_error());
	}



// FUNCTION C9: function for creating a class ---------------------------------------------------------------------------------------------



	function createclass($course_id,$account_id,$template_id,$class_name)
	{
		$createclass_query = "";
		$createclass_query_[0] = "INSERT INTO class(course_id,account_id,template_id,class_name) ";
		$createclass_query_[1] = "VALUES('".$course_id."','".$account_id."','".$template_id."','".$class_name."')";
		$createclass_query = $createclass_query_[0].$createclass_query_[1];
		$insert = mysql_query($createclass_query) or die(mysql_error());
	}



// FUNCTION C10: function for creating a schedule -----------------------------------------------------------------------------------------



	function createschedule($class_id,$room_number,$day,$start_time,$end_time)
	{
		$createschedule_query = "";
		$createschedule_query_[0] = "INSERT INTO schedule(class_id,room_number,day,start_time,end_time) VALUES('";
		$createschedule_query_[1] = $class_id."','".$room_number."','".$day."','".$start_time."','".$end_time."')";
		$createschedule_query = $createschedule_query_[0].$createschedule_query_[1];;	
		$insert = mysql_query($createschedule_query) or die(mysql_error());
	}



// FUNCTION C11: function for creating a team --------------------------------------------------------------------------------------------



	function createteam($class_id,$team_name)
	{
		$createteam_query = "INSERT INTO team(class_id,team_name) VALUES('".$class_id."','".$team_name."')";
		$insert = mysql_query($createteam_query) or die(mysql_error());		
	}



// FUNCTION C12: function for creating a grade --------------------------------------------------------------------------------------------


	
	function creategrade($class_id,$major_id,$minor_id,$account_id,$percentage,$grade)
	{
		$creategrade_query = "";
		$creategrade_query_[0] = "INSERT INTO grade(class_id,major_id,minor_id,account_id,percentage,grade) ";
		$creategrade_query_[1] = "VALUES ('".$class_id."','".$major_id."','".$minor_id."','";
		$creategrade_query_[2] = $account_id."','".$percentage."','".$grade."')";
		$creategrade_query =  $creategrade_query_[0].$creategrade_query_[1].$creategrade_query_[2];
		$insert = mysql_query($creategrade_query) or die(mysql_error());	
	}



// FUNCTION C13: function for creating a take ---------------------------------------------------------------------------------------------



	function createtake($class_id,$account_id)
	{
		$createtake_query = "INSERT INTO take(class_id,account_id) VALUES('".$class_id."','".$account_id."')";
		$insert = mysql_query($createtake_query) or die(mysql_error());	
	}



// FUNCTION C14: function for creating a member ---------------------------------------------------------------------------------------------



	function createmember($team_id,$account_id)
	{
		$createmember_query = "INSERT INTO member(team_id,account_id) VALUES('".$team_id."','".$account_id."')";
		$insert = mysql_query($createmember_query) or die(mysql_error());	
	}



// MODIFY FUNCTIONS -----------------------------------------------------------------------------------------------------------------------



// FUNCTION M1: fuction for modifying account ---------------------------------------------------------------------------------------------



	function modifyaccount($account_id, 
			       $email, 
                               $username,
                               $password,
                               $first_name,
                               $middle_name,
                               $last_name,
			       $sex, 
			       $birthday,
			       $contact_number,
                               $country, 
                               $city_province,
                               $municipality,
                               $street)
	{
		$modifyaccount_query = "";
		$modifyaccount_query_[0] = "UPDATE account SET ";
		$modifyaccount_query_[1] = "email = '".$email."', ";
		$modifyaccount_query_[2] = "username = '".$username."', ";
		$modifyaccount_query_[3] = "password = '".$password."', ";
		$modifyaccount_query_[4] = "first_name = '".$first_name."', ";
		$modifyaccount_query_[5] = "middle_name = '".$middle_name."', ";
		$modifyaccount_query_[6] = "last_name = '".$last_name."', ";
		$modifyaccount_query_[7] = "sex = '".$sex."', ";
		$modifyaccount_query_[8] = "birthday = '".$birthday."', ";
		$modifyaccount_query_[9] = "contact_number = '".$contact_number."', ";
		$modifyaccount_query_[10] = "country = '".$country."', ";
		$modifyaccount_query_[11] = "city_province = '".$city_province."', ";
		$modifyaccount_query_[12] = "municipality = '".$municipality."', ";
		$modifyaccount_query_[13] = "street = '".$street."' ";
		$modifyaccount_query_[14] = "WHERE account_id = ".$account_id."";

		for ($i=0; $i<=14; $i++)
		{
			$modifyaccount_query = $modifyaccount_query.$modifyaccount_query_[$i];
			
  		}
		$update = mysql_query($modifyaccount_query) or die(mysql_error());
	}



// FUNCTION M2: fuction for modifying a school --------------------------------------------------------------------------------------------


	
	function modifyschool($school_id,
			       $school_name,
			       $college_name,
			       $department_name,
			       $president,
			       $principal,
			       $dean,
			       $college_secretary,
			       $department_chair,
			       $country,
			       $zip_code,
			       $city_province,
			       $municipality,
			       $street,
			       $description,
			       $contact_info_1,
			       $contact_info_2,
			       $contact_info_3)
	{
		$modifyschool_query = "";
		$modifyschool_query_[0] = "UPDATE school SET ";
		$modifyschool_query_[1] = "school_name = '".$school_name."', ";
		$modifyschool_query_[2] = "college_name = '".$college_name."', ";
		$modifyschool_query_[3] = "department_name = '".$department_name."', ";
		$modifyschool_query_[4] = "president = '".$president."', ";
		$modifyschool_query_[5] = "principal = '".$principal."', ";
		$modifyschool_query_[6] = "dean = '".$dean."', ";
		$modifyschool_query_[7] = "college_secretary = '".$college_secretary."', ";
		$modifyschool_query_[8] = "department_chair = '".$department_chair."', ";
		$modifyschool_query_[9] = "country = '".$country."', ";
		$modifyschool_query_[10] = "zip_code = '".$zip_code."', ";
		$modifyschool_query_[11] = "city_province = '".$city_province."', ";
		$modifyschool_query_[12] = "municipality = '".$municipality."', ";
		$modifyschool_query_[13] = "street = '".$street."', ";
		$modifyschool_query_[14] = "description = '".$description."', ";
		$modifyschool_query_[15] = "contact_info_1 = '".$contact_info_1."', ";
		$modifyschool_query_[16] = "contact_info_2 = '".$contact_info_2."', ";
		$modifyschool_query_[17] = "contact_info_3 = '".$contact_info_3."' ";
		$modifyschool_query_[18] = "WHERE school_id = ".$school_id."";

		for ($i=0; $i<=18; $i++)
		{
			$modifyschool_query = $modifyschool_query.$modifyschool_query_[$i];
  		}
		
		$update = mysql_query($modifyschool_query) or die(mysql_error());
	}



// FUNCTION M3: functon for modifying course ----------------------------------------------------------------------------------------------



	function modifycourse($course_id,$course_name, $school_id)
	{
		$modifycourse_query_[0] = "UPDATE course SET course_name='".$course_name;
		$modifycourse_query_[1] = "', school_id = '".$school_id."' WHERE course_id = '".$course_id."'";
		$modifycourse_query = $modifycourse_query_[0].$modifycourse_query_[1];
		$update = mysql_query($modifycourse_query) or die(mysql_error());
	}



// FUNCTION M4: function for modifying a scheme --------------------------------------------------------------------------------------



	function modifyscheme($scheme_id,$scheme_name)
	{
		$modifycheme_query = "UPDATE scheme SET scheme_name = '".$scheme_name."' WHERE scheme_id =".$scheme_id;
		$update = mysql_query($modifycheme_query) or die(mysql_error());
	}



// FUNCTION M5: function modifying a block ------------------------------------------------------------------------------------------------



	function modifyblock($block_id,$lower_bound,$upper_bound,$mark,$lower_condition,$upper_condition)
	{
		$modifyblock_query = "";
		$modifyblock_query_[0] = "UPDATE block SET ";
		$modifyblock_query_[1] = "lower_bound = '".$lower_bound."', ";
		$modifyblock_query_[2] = "upper_bound = '".$upper_bound."', ";
		$modifyblock_query_[3] = "mark = '".$mark."', ";
		$modifyblock_query_[4] = "lower_condition = '".$lower_condition."', ";
		$modifyblock_query_[5] = "upper_condition = '".$upper_condition."' ";
		$modifyblock_query_[6] = "WHERE block_id = ".$block_id." ";

		for ($i=0; $i<=6; $i++)
		{
			$modifyblock_query = $modifyblock_query.$modifyblock_query_[$i];
  		}

		$update = mysql_query($modifyblock_query) or die(mysql_error());		
	}



// FUNCTION M6: function for modifying a template -------------------------------------------------------------------------------------------



	function modifytemplate($template_id,$template_name)
	{
		$modifytemplate_query = "UPDATE template SET template_name = '".$template_name."' WHERE template_id =".$template_id;
		$update = mysql_query($modifytemplate_query) or die(mysql_error());
	}



// FUNCTION M7: function for modifying a major requirement --------------------------------------------------------------------------------



	function modifymajor($major_id,$name,$percentage)
	{
		$modifymajor_query = "";
		$modifymajor_query_[0] = "UPDATE major SET name = '".$name."', percentage = '".$percentage."' ";		
		$modifymajor_query_[1] = "WHERE major_id = '".$major_id."'";
		$modifymajor_query = $modifymajor_query_[0].$modifymajor_query_[1];
		$update = mysql_query($modifymajor_query) or die(mysql_error());
	}



// FUNCTION M8: function for modifying a minor requirement --------------------------------------------------------------------------------



	function modifyminor($minor_id,$name,$maxrawscore)
	{
		$modifyminor_query = "";
		$modifyminor_query_[0] = "UPDATE minor SET name = '".$name."', maxrawscore = '".$maxrawscore."' ";		
		$modifyminor_query_[1] = "WHERE minor_id = '".$minor_id."'";
		$modifyminor_query = $modifyminor_query_[0].$modifyminor_query_[1];
		$update = mysql_query($modifyminor_query) or die(mysql_error());
	}	



// FUNCTION M9: function for modifying a class --------------------------------------------------------------------------------------------



	function modifyclass($class_id,$class_name,$year,$period,$building_name)
	{
		$modifyclass_query = "";
		$modifyclass_query_[0] = "UPDATE class SET ";
		$modifyclass_query_[1] = "class_name = '".$class_name."', ";
		$modifyclass_query_[2] = "year = '".$year."', ";
		$modifyclass_query_[3] = "period = '".$period."', ";
		$modifyclass_query_[4] = "building_name = '".$building_name."' ";
		$modifyclass_query_[5] = "WHERE class_id = ".$class_id." ";

		for ($i=0; $i<=6; $i++)
		{
			$modifyclass_query = $modifyclass_query.$modifyclass_query_[$i];
  		}
		$update = mysql_query($modifyclass_query) or die(mysql_error());	
	}



// FUNCTION M10: function for modifying schedule ------------------------------------------------------------------------------------------



	function modifyschedule($schedule_id,$room_number,$day,$start_time,$end_time)
	{
		$modifyschedule_query = "";
		$modifyschedule_query_[0] = "UPDATE schedule SET room_number = '".$room_number."', day = '".$day."',";		
		$modifyschedule_query_[1] = "start_time ='".$start_time."', end_time ='".$end_time."' WHERE schedule_id = '".$schedule_id."'";
		$modifyschedule_query = $modifyschedule_query_[0].$modifyschedule_query_[1];
		$update = mysql_query($modifyschedule_query) or die(mysql_error());	
	}



// FUNCTION M11: function for modifying a team --------------------------------------------------------------------------------------------



	function modifyteam($team_id,$team_name)
	{
		$modifyteam_query = "UPDATE team SET team_name = '".$team_name."' WHERE team_id =".$team_id;
		$update = mysql_query($modifyteam_query) or die(mysql_error());
	}


	
// FUNCTION M12: function for modifying a grade -------------------------------------------------------------------------------------------



	function modifygrade($grade_id,$percentage,$grade)
	{
		$modifygrade_query = "";
		$modifygrade_query_[0] = "UPDATE grade SET percentage = '".$percentage."', grade = '".$grade."' ";		
		$modifygrade_query_[1] = "WHERE grade_id = '".$grade_id."'";
		$modifygrade_query = $modifygrade_query_[0].$modifygrade_query_[1];
		$update = mysql_query($modifygrade_query) or die(mysql_error());
	}

?>
